﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Streaming_Service.SQL_Ting {
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    using Streaming_Service.Enum;
    using Streaming_Service.User;

    public class UserQueries {
        public static UserSession Login(string email, string password, SqlHandler sh) {
            var SQL = "SELECT ui.* FROM userinfo AS ui " + 
                "LEFT JOIN useractivation AS ua ON ua.userid = ui.id " +
                "WHERE " + 
                    "ui.email = @email "  +
                    "AND ui.pwhash = dbo.md5(ui.salt + dbo.md5(@password)) " +
                    "AND ua.code IS NULL";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("email", SqlDbType.VarChar).Value = email;
            c.Parameters.Add("password", SqlDbType.VarChar).Value = password;

            var session = new UserSession();
            var salt = "";

            var r = c.ExecuteReader();
            while (r.Read()) {
                session.UserId = r.GetInt32(r.GetOrdinal("id"));
                session.IsAdmin = (r.GetByte(r.GetOrdinal("isadmin")) != 0);
                session.Birthday = r.GetDateTime(r.GetOrdinal("birthdate"));
                session.Email = r.GetString(r.GetOrdinal("email"));
                session.Name = r.GetString(r.GetOrdinal("name"));
                salt = r.GetString(r.GetOrdinal("salt"));
                break;
            }
            r.Close();

            if (session.UserId > -1) {
                var token = CreateUserSession(session.UserId, salt, sh);
                if (!string.IsNullOrEmpty(token)) {
                    session.Token = token;
                    return session;
                }
            }
            return null;
        }

        public static void Logout(UserSession session, SqlHandler sh) {
            DeleteUserSession(session, sh);
        }

        public static string CreateUserSession(int userid, string salt, SqlHandler sh) {
            var token = DBFunc.MD5(DBFunc.MD5("" + userid + DBFunc.Rand(10000, 99999)) + salt + DBFunc.Rand(10, 500));
            var time = DBFunc.UnixTimestamp();

            var SQL = "INSERT INTO usersession VALUES(@uid, @token, @time)";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userid;
            c.Parameters.Add("token", SqlDbType.VarChar).Value = token;
            c.Parameters.Add("time", SqlDbType.Int).Value = time;

            var r = c.ExecuteNonQuery();
            return r > 0 ? token : string.Empty;
        }

        public static UserSession VerifyUserSession(UserSession session, SqlHandler sh) {
            if (session == null || session.Token == null) return null;
            ClearInactiveUserSession(sh);

            var SQL = "SELECT * FROM userinfo " + 
                "WHERE id = (" + 
                    "SELECT userid FROM usersession " + 
                    "WHERE userid = @uid AND token = @token" + 
                ")";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = session.UserId;
            c.Parameters.Add("token", SqlDbType.VarChar).Value = session.Token;

            var newSession = new UserSession();
            newSession.Token = session.Token;

            var r = c.ExecuteReader();
            while (r.Read()) {
                newSession.UserId = r.GetInt32(r.GetOrdinal("id"));
                newSession.IsAdmin = (r.GetByte(r.GetOrdinal("isadmin")) != 0);
                newSession.Birthday = r.GetDateTime(r.GetOrdinal("birthdate"));
                newSession.Email = r.GetString(r.GetOrdinal("email"));
                newSession.Name = r.GetString(r.GetOrdinal("name"));
                break;
            }
            r.Close();

            if (newSession.UserId > -1) {
                UpdateUserSession(newSession, sh);
                return newSession;
            }

            return null;
        }

        public static void UpdateUserSession(UserSession session, SqlHandler sh) {
            var SQL = "UPDATE usersession " + 
                "SET lastaction = @time " + 
                "WHERE userid = @uid AND token = @token";

            var time = DBFunc.UnixTimestamp();

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = session.UserId;
            c.Parameters.Add("token", SqlDbType.VarChar).Value = session.Token;
            c.Parameters.Add("time", SqlDbType.Int).Value = time;

            c.ExecuteNonQuery();
        }

        public static void DeleteUserSession(UserSession session, SqlHandler sh) {
            var SQL = "DELETE FROM usersession " + 
                "WHERE userid = @uid AND token = @token";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = session.UserId;
            c.Parameters.Add("token", SqlDbType.VarChar).Value = session.Token;

            c.ExecuteNonQuery();
        }

        public static void ClearInactiveUserSession(SqlHandler sh) {
            var time = DBFunc.UnixTimestamp();
            var delSeconds = 24 * 60 * 60; // 1 day

            var SQL = "DELETE FROM usersession " + 
                "WHERE (@time - lastaction) > @delseconds";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("time", SqlDbType.Int).Value = time;
            c.Parameters.Add("delseconds", SqlDbType.Int).Value = delSeconds;

            c.ExecuteNonQuery();
        }

        public static UserEntry GetUserInfo(int userId, SqlHandler sh) {
            var SQL = "SELECT * FROM userinfo WHERE id = @uid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userId;

            var entry = new UserEntry();

            var r = c.ExecuteReader();
            while(r.Read()) {
                entry = GetUserEntry(r);
            }
            r.Close();

            return entry;
        }

        public static UserEntry GetUserInfo(string email, SqlHandler sh) {
            var SQL = "SELECT * FROM userinfo WHERE email = @email";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("email", SqlDbType.VarChar).Value = email;

            var entry = new UserEntry();

            var r = c.ExecuteReader();
            while (r.Read()) {
                entry = GetUserEntry(r);
            }
            r.Close();

            return entry;
        }

        public static List<UserEntry> ListUsers(SqlHandler sh) {
            var SQL = "SELECT * FROM userinfo AS ui";

            var users = new List<UserEntry>();

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteReader();
            while (r.Read()) {
                users.Add(GetUserEntry(r));
            }
            r.Close();

            return users;
        }

        public static List<UserEntry> ListOnlineUsers(int inactivityCutOff, SqlHandler sh) {
            ClearInactiveUserSession(sh);

            var SQL = "SELECT ui.* FROM userinfo AS ui, usersession AS us " + 
                "WHERE " + 
                    "(@time - us.lastaction) < @cutoff AND ui.id = us.userid";

            var users = new List<UserEntry>();

            var time = DBFunc.UnixTimestamp();
            var cutoff = inactivityCutOff * 60;
            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("time", SqlDbType.Int).Value = time;
            c.Parameters.Add("cutoff", SqlDbType.Int).Value = cutoff;

            var r = c.ExecuteReader();
            while (r.Read()) {
                users.Add(GetUserEntry(r));
            }
            r.Close();

            return users;
        }

        public static UserEntry GetUserEntry(SqlDataReader r) {
            var ue = new UserEntry();
            try {
                ue.Id = r.GetInt32(r.GetOrdinal("id"));
                ue.Name = r.GetString(r.GetOrdinal("name"));
                ue.Email = r.GetString(r.GetOrdinal("email"));
                ue.Birthday = r.GetDateTime(r.GetOrdinal("birthdate"));
                ue.IsAdmin = (r.GetByte(r.GetOrdinal("isadmin")) != 0);
            } catch {}
            return ue;
        }

        public static UserReturnCode CreateAccount(string name, string password, string email, DateTime birthday, SqlHandler sh) {
            if(!string.IsNullOrEmpty(email)) email = email.ToLower();
            if(!UserRegex.ValidName(name)) return UserReturnCode.InvalidName;
            if(!UserRegex.ValidPassword(password)) return UserReturnCode.InvalidPassword;
            if(!UserRegex.ValidEmail(email)) return UserReturnCode.InvalidEmail;
            if(!UserEntry.IsValidBirthday(birthday)) return UserReturnCode.InvalidBirthday;
            if(EmailInUse(email, sh)) return UserReturnCode.EmailInUse;
            
            var SQL = "INSERT INTO USERINFO (name, pwhash, birthdate, email, salt) "
                      + "VALUES(@name, @pwhash, @bday, @email, @salt);"
                      + "SELECT CAST(SCOPE_IDENTITY() AS INT)";

            var salt = DBFunc.RandStrXtd(5);
            var pwhash = DBFunc.MD5(salt + DBFunc.MD5(password));

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("name", SqlDbType.VarChar).Value = name;
            c.Parameters.Add("pwhash", SqlDbType.VarChar).Value = pwhash;
            c.Parameters.Add("bday", SqlDbType.DateTime).Value = birthday;
            c.Parameters.Add("email", SqlDbType.VarChar).Value = email;
            c.Parameters.Add("salt", SqlDbType.VarChar).Value = salt;

            var r = c.ExecuteScalar();
            if(r is int) {
                var userid = (int)r;
                if(RequireUserActivation(userid, sh)) {
                    return UserReturnCode.Success;
                }
            }
            return UserReturnCode.Failure;
        }

        public static bool RequireUserActivation(int userid, SqlHandler sh) {
            if (!UserExists(userid, sh)) return false;

            var SQL = "INSERT INTO useractivation (userid, code) VALUES(@uid, @code)";
            
            var code = DBFunc.RandStr(6);

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userid;
            c.Parameters.Add("code", SqlDbType.VarChar).Value = code;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool ActivationRequired(int userId, SqlHandler sh) {
            var SQL = "SELECT userid FROM useractivation WHERE userid = @uid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userId;

            var r = c.ExecuteScalar();

            return r is int;
        }

        public static string GetActivationCode(int userId, SqlHandler sh) {
            var SQL = "SELECT code FROM useractivation WHERE userid = @uid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userId;

            var r = c.ExecuteScalar();

            return r is string ? (string)r : null;
        }

        public static bool ActivateAccount(int userid, string code, SqlHandler sh) {
            var SQL = "DELETE FROM useractivation " 
                + "WHERE userid = @uid AND code = @code";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userid;
            c.Parameters.Add("code", SqlDbType.VarChar).Value = code;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static UserReturnCode UpdateAccount(int userid, string newPassword, string newEmail, DateTime? newBirthday, XBool isAdmin, SqlHandler sh) {
            if (!UserExists(userid, sh)) return UserReturnCode.InvalidUserSpecified;

            var updVars = new Dictionary<string, QuickField>();

            if(!string.IsNullOrEmpty(newPassword)) {
                if(UserRegex.ValidPassword(newPassword)) {
                    updVars["pw"] = new QuickField(newPassword, "pwhash = dbo.md5(salt + dbo.md5(@pw))", SqlDbType.VarChar);
                }else {
                    return UserReturnCode.InvalidPassword;
                }
            }

            if (!string.IsNullOrEmpty(newEmail)) {
                if (UserRegex.ValidEmail(newEmail)) {
                    if (!EmailInUse(newEmail, sh)) {
                        updVars["email"] = new QuickField(newEmail, "email = @email", SqlDbType.VarChar);
                    }else {
                        return UserReturnCode.EmailInUse;
                    }
                } else {
                    return UserReturnCode.InvalidEmail;
                }
            }

            if (newBirthday != null) {
                if (UserEntry.IsValidBirthday(newBirthday)) {
                    updVars["bday"] = new QuickField(newBirthday, "birthdate = @bday", SqlDbType.DateTime);
                } else {
                    return UserReturnCode.InvalidBirthday;
                }
            }

            if (isAdmin != XBool.Ignore) {
                updVars["isadmin"] = new QuickField(isAdmin == XBool.True ? 1 : 0, "isadmin = @isadmin", SqlDbType.TinyInt);
            }

            if(updVars.Count > 0) {
                var SQL = new StringBuilder("UPDATE userinfo SET ");
                foreach(var updVar in updVars) {
                    SQL.Append(updVar.Value.SQL + ", ");
                }
                SQL.Remove(SQL.Length - 2, 1);
                SQL.Append("WHERE id = @uid");

                var c = sh.CreateCommand(SQL.ToString());
                c.Parameters.Add("uid", SqlDbType.Int).Value = userid;
                foreach(var updVar in updVars) {
                    c.Parameters.Add(updVar.Key, updVar.Value.DbType).Value = updVar.Value.Value;
                }

                var r = c.ExecuteNonQuery();

                return (r > 0 ? UserReturnCode.Success : UserReturnCode.Failure);
            }
            return UserReturnCode.NothingToUpdate;
        }

        public static UserReturnCode DeleteAccount(int userId, string password, bool adminOverride, SqlHandler sh) {
            if (!UserExists(userId, sh)) return UserReturnCode.InvalidUserSpecified;
            if (!adminOverride && !VerifyPassword(userId, password, sh)) return UserReturnCode.InvalidPassword;

            var SQL = "DELETE FROM userinfo WHERE id = @uid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userId;

            var r = c.ExecuteNonQuery();

            return (r > 0 ? UserReturnCode.Success : UserReturnCode.Failure);
        }

        public static bool UserExists(int userId, SqlHandler sh) {
            var SQL = "SELECT id FROM userinfo WHERE id = @id";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("id", SqlDbType.Int).Value = userId;

            var r = c.ExecuteScalar();
            if (r is int) {
                return (int)r == userId;
            }

            return false;
        }

        public static bool VerifyPassword(int userId, string password, SqlHandler sh) {
            if(userId < 1 || String.IsNullOrEmpty(password)) return false;

            var SQL = "SELECT id FROM userinfo " +
            "WHERE id = @uid AND pwhash = dbo.md5(salt + dbo.md5(@pw))";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("uid", SqlDbType.Int).Value = userId;
            c.Parameters.Add("pw", SqlDbType.VarChar).Value = password;

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r == userId;
            }

            return false;
        }

        public static bool EmailInUse(string email, SqlHandler sh) {
            var SQL = "SELECT id FROM userinfo WHERE email = @email";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("email", SqlDbType.VarChar).Value = email;

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r > 0;
            }

            return false;
        }
    }
}